--EXEC sp_lot_yield_by_lot_number 0
ALTER  proc sp_lot_yield_by_lot_number
@BASE_PRODUCT_ID INT
AS
begin

	declare @results Table( Wafer_Lot_No varchar(50),
		Fab_out_Wafers 	INT,
		DIE_REV 	INT,
		WAFER_OUTDATE 	VARCHAR(50),
		WAFERSORT_DATE VARCHAR(50),
		WAFERSORT_TEST_PGM	varchar(50),
		TOTAL_GOOD_DIE_QTY 	int,
		WAFERSORT_YILED	FLOAT,
		MARKING	VARCHAR(100),
		ASSEMBLY_LOT_NOS VARCHAR(50),
		ASSEMBLY_IN_QTY	 INT,
		ASSEMBLY_YIELD	FLOAT,
		TEST_QTY_IN		INT,
		TEST_DATE_IN    DATETIME,
		BIN1			INT,
		BIN2			INT,
		BIN3			INT,
		BIN4			INT,
		BIN5			INT,
		GOOD_UNITS		INT,
		UNCONUT_UNITS	INT,
		TEST_YIELD		FLOAT,
		TESTER			VARCHAR(50),
		TEST_PGM		VARCHAR(50),
		SCAN_OUTQTY		INT,
		SCAN_YIELD		FLOAT,
		CUM_YIELD		FLOAT)

	DECLARE @LOT_NUMBER_TABLE TABLE ( Fab_Lot_Number VARCHAR(50), TIMESTAMP DATETIME, PROCESSSTEP_ID INT )
	
	declare @lot_id_cursor 	 CURSOR
	declare @aasy_lot_cursor CURSOR
	declare @test_lot_cursor CURSOR
	
	declare @fab_lot VARCHAR(50)
	declare @PS_TYPE VARCHAR(50)
	DECLARE @PROCESSSTEP_ID INT
	
	declare	@fab_date_out DATETIME, @SORT_DATE_OUT DATETIME, @TEST_DATEIN DATETIME
	DECLARE @fab_out_qty INT, @BIN_RECEIVE_WAFER_QTY INT , @GROSS_DIE INT, @GOOD_DIE INT ,@TOTALASSY_INQTY INT, @ASSY_QTY INT, @TOTALTEST_INQTY INT, @TEST_QTY INT, @SCAN_OUTQTY INT
	DECLARE @SORTTEST_PGM VARCHAR(50), @TEST_PGM VARCHAR(50), @TESTER VARCHAR(50)
	DECLARE @SORT_YIELD FLOAT, @ASSY_YIELD FLOAT, @TEST_YIELD FLOAT, @SCAN_YIELD FLOAT,@CUM_YIELD FLOAT
	DECLARE @BIN1 INT,@BIN2 INT,@BIN3 INT,@BIN4 INT,@BIN5 INT, @GOOD_UNITS INT, @UNCOUNT_UNITS INT
	DECLARE @BIN1_QTY INT,@BIN2_QTY INT,@BIN3_QTY INT,@BIN4_QTY INT,@BIN5_QTY INT, @DAMAGE_QTY INT
	DECLARE @ASSEMBLY_LOT VARCHAR(50),@ASSY_LOT VARCHAR(50)
	DECLARE @MARKING VARCHAR(100),@PART_NUMBER VARCHAR(50), @DATE_CODE VARCHAR(50), @DIE_REV VARCHAR(10)
	DECLARE @TID VARCHAR(50),@TPGM VARCHAR(50), @PART_NO VARCHAR(50), @DCODE VARCHAR(50)
	
	IF @BASE_PRODUCT_ID > 0
	BEGIN
		INSERT INTO @LOT_NUMBER_TABLE
		SELECT distinct Fab_Lot_Number, LT.TIMESTAMP, PS.PROCESSSTEP_ID FROM LOT_TABLE LT, PROCESS_STEP PS,  PRODUCT P, SHIPPED_LOT SL WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID= SL.LOT_ID AND P.P_PARENT_PRODUCT_ID  = @BASE_PRODUCT_ID AND PS_TYPE_NAME IN ('FAB')
	END
	ELSE
	BEGIN
		INSERT INTO @LOT_NUMBER_TABLE
		SELECT distinct Fab_Lot_Number, LT.TIMESTAMP, PS.PROCESSSTEP_ID  FROM LOT_TABLE LT, PROCESS_STEP PS, SHIPPED_LOT SL WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID= SL.LOT_ID  AND PS_TYPE_NAME IN ('FAB')
	END
	--SELECT * FROM @LOT_NUMBER_TABLE	ORDER BY TIMESTAMP
	
	set @lot_id_cursor = CURSOR FOR SELECT Fab_Lot_Number, PROCESSSTEP_ID FROM @LOT_NUMBER_TABLE ORDER BY TIMESTAMP
	
	open @lot_id_cursor
	fetch next from @lot_id_cursor into @FAB_LOT, @PROCESSSTEP_ID
	while @@fetch_status = 0
	begin
				
		SELECT @GROSS_DIE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND PARAMETER_KEY = 'Gross Die/Wafer'
	
		SELECT @FAB_OUT_QTY = SHIP_QTY, @FAB_DATE_OUT = ISNULL( SL.SHIP_DATE, SL.TIMESTAMP ) FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE LT.LOT_ID= SL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT

		SELECT @SORT_DATE_OUT = DATE_IN, @BIN_RECEIVE_WAFER_QTY = WAFER_QTY_IN, @GOOD_DIE = GOOD_DIE_QTY, @SORTTEST_PGM = SL_TEST_PROGRAM, @SORT_YIELD = SL_YIELD FROM SORT_LOT SRL, LOT_TABLE LT WHERE LT.LOT_ID= SRL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
	
		IF @SORT_DATE_OUT IS NULL
		BEGIN
			SELECT @SORT_DATE_OUT = DATE_OUT FROM SORT_LOT SRL, LOT_TABLE LT WHERE LT.LOT_ID= SRL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT 
		END
	
		/*IF @BIN_RECEIVE_WAFER_QTY IS NOT NULL AND @FAB_OUT_QTY < @BIN_RECEIVE_WAFER_QTY
		BEGIN				
				SET @FAB_OUT_QTY = @BIN_RECEIVE_WAFER_QTY
		END*/
	
		SET @TOTALASSY_INQTY = 0
		set @ASSEMBLY_LOT = ''
		set @aasy_lot_cursor = CURSOR FOR SELECT LOT_NUMBER FROM LOT_TABLE LT, ASSEMBLY_LOT AL WHERE LT.LOT_ID= AL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
		open @aasy_lot_cursor
		fetch next from @aasy_lot_cursor into @ASSY_LOT 
		while @@fetch_status = 0
		begin								
			SELECT @ASSY_QTY  = MAX(QTY_IN) FROM LOT_TABLE_HISTORY LTH, ASSEMBLY_LOT AL WHERE LTH.LOT_ID= AL.LOT_ID AND LOT_NUMBER = @ASSY_LOT
			IF @ASSY_QTY IS NULL
			BEGIN
				SELECT @ASSY_QTY = QTY_IN FROM LOT_TABLE LT, ASSEMBLY_LOT AL WHERE LT.LOT_ID= AL.LOT_ID AND LOT_NUMBER = @ASSY_LOT 
			END
			
			SET @TOTALASSY_INQTY = @TOTALASSY_INQTY + @ASSY_QTY
		
			SET @ASSEMBLY_LOT = @ASSEMBLY_LOT + @ASSY_LOT + ','
				
			set @ASSY_LOT = NULL
			set @ASSY_QTY = null
			fetch next from @aasy_lot_cursor into @ASSY_LOT
		end
		close @aasy_lot_cursor
		deallocate @aasy_lot_cursor

		--removing last Comma
		IF @ASSEMBLY_LOT <> ''
		BEGIN
			SET @ASSEMBLY_LOT = substring( @ASSEMBLY_LOT, 1,len(@ASSEMBLY_LOT)-1 )
		END
	
			
		set @test_lot_cursor = CURSOR FOR SELECT SUM(QTY_IN), MAX(DATE_IN), PART_NUMBER, DATE_CODE, FTL_TEST_PROGRAM, TESTER_ID, SUM(BIN_1), SUM(BIN_2),SUM(BIN_3),SUM(BIN_4), SUM(BIN_5),SUM(ISNULL(DAMAGE_QTY, 0) + ISNULL(LOSS_QTY,0) )
										FROM LOT_TABLE LT, Product P, PROCESS_STEP PS, FINAL_TEST_LOT FTL,TEST_INFORMATIVE_BINNING TIB 
										WHERE PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.ProcessStep_ID = LT.ProcessStep_ID AND PS.PS_TYPE = 'FT' AND LT.LOT_ID= FTL.LOT_ID AND FTL.LOT_ID = TIB.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT  GROUP BY PART_NUMBER, DATE_CODE, FTL_TEST_PROGRAM, TESTER_ID
		SET @TOTALTEST_INQTY = 0
		SET @BIN1 = 0 SET @BIN2 = 0  SET @BIN3 = 0 SET @BIN4 = 0 SET @BIN5 = 0 SET @UNCOUNT_UNITS = 0
		SET @MARKING = '' SET @PART_NUMBER = '' SET @DATE_CODE = '' SET @TEST_PGM = '' SET @TESTER = ''
	
		open @test_lot_cursor
		fetch next from @test_lot_cursor into @TEST_QTY, @TEST_DATEIN, @PART_NO, @DCODE, @TPGM, @TID,@BIN1_QTY,@BIN2_QTY,@BIN3_QTY,@BIN4_QTY,@BIN5_QTY,@DAMAGE_QTY
		while @@fetch_status = 0
		begin		
			SET @TOTALTEST_INQTY 	= @TOTALTEST_INQTY + @TEST_QTY
			SET @BIN1		= @BIN1 + @BIN1_QTY
			SET @BIN2		= @BIN2 + @BIN2_QTY
			SET @BIN3		= @BIN3 + @BIN3_QTY
			SET @BIN4		= @BIN4 + @BIN4_QTY
			SET @BIN5		= @BIN5 + @BIN5_QTY
			SET @UNCOUNT_UNITS	= @UNCOUNT_UNITS + @DAMAGE_QTY

			IF  charindex(@PART_NO, @PART_NUMBER) = 0
			BEGIN
				SET @PART_NUMBER = @PART_NUMBER + @PART_NO + ','	
			END
			IF  charindex(@DCODE, @DATE_CODE) = 0
			BEGIN
				SET @DATE_CODE = @DATE_CODE + @DCODE +','	
			END
			IF  charindex(@TPGM, @TEST_PGM) = 0
			BEGIN
				SET @TEST_PGM = @TEST_PGM + @TPGM  + ','	
			END
			IF  charindex(@TID, @TESTER) = 0
			BEGIN
				SET @TESTER = @TESTER + @TID +','	
			END
			SET @TEST_QTY = NULL 
			SET @PART_NO = NULL  
			SET @DCODE = NULL  
			SET @TPGM = NULL  
			SET @TID = NULL  
			SET @BIN1_QTY = NULL 
			SET @BIN2_QTY = NULL 
			SET @BIN3_QTY = NULL 
			SET @BIN4_QTY = NULL 
			SET @BIN5_QTY = NULL 
			SET @DAMAGE_QTY = NULL 			
			fetch next from @test_lot_cursor into @TEST_QTY, @TEST_DATEIN , @PART_NO, @DCODE, @TPGM, @TID,@BIN1_QTY,@BIN2_QTY,@BIN3_QTY,@BIN4_QTY,@BIN5_QTY,@DAMAGE_QTY
		end
	
		close @test_lot_cursor
		deallocate @test_lot_cursor
	
		--removing last Comma
		IF @PART_NUMBER <> ''
		BEGIN
			SET  @PART_NUMBER = substring(@PART_NUMBER, 1,len(@PART_NUMBER)-1)
		END
		IF @DATE_CODE <> ''
		BEGIN
			SET  @DATE_CODE = substring(@DATE_CODE, 1,len(@DATE_CODE)-1)
		END
		IF @TEST_PGM <> ''
		BEGIN
			SET  @TEST_PGM = substring(@TEST_PGM, 1,len(@TEST_PGM)-1)
		END
		IF @TESTER <> ''
		BEGIN
			SET  @TESTER = substring(@TESTER, 1,len(@TESTER)-1)
		END
		SET @MARKING =  @PART_NUMBER + '       ' + @DATE_CODE
	
		--Yield Calculating
		IF @TOTALTEST_INQTY IS NOT NULL AND @TOTALASSY_INQTY > 0
		BEGIN
			SET @ASSY_YIELD = ROUND( ( @TOTALTEST_INQTY * 100.00 ) / @TOTALASSY_INQTY , 1)	
		END
		
		IF @BIN2 IS NOT NULL 
		BEGIN
			SET @GOOD_UNITS = @BIN1+@BIN2+@BIN3
			IF @TOTALTEST_INQTY > 0
			BEGIN
				SET @TEST_YIELD = ROUND( ( @GOOD_UNITS * 100.00 ) / @TOTALTEST_INQTY , 1)
			END
		END
	
		SELECT @SCAN_OUTQTY = SUM(QTY_OUT) FROM LOT_TABLE LT, PROCESS_STEP PS, FINAL_TEST_LOT FTL
		WHERE PS.ProcessStep_ID = LT.ProcessStep_ID AND PS.PS_TYPE = 'Ball Scan' AND LT.LOT_ID= FTL.LOT_ID AND FAB_LOT_NUMBER = @FAB_LOT
	
		IF @SCAN_OUTQTY IS NOT NULL
		BEGIN
			SET @SCAN_YIELD = ROUND( ( @SCAN_OUTQTY * 100.00 ) / @GOOD_UNITS , 1)
			SET @CUM_YIELD  = ROUND( ( @SCAN_OUTQTY * 100.00 ) / (@GROSS_DIE * @FAB_OUT_QTY) , 1)
		END
	
		INSERT @RESULTS ( WAFER_LOT_NO, FAB_OUT_WAFERS, DIE_REV, WAFER_OUTDATE, WAFERSORT_DATE, WAFERSORT_TEST_PGM, TOTAL_GOOD_DIE_QTY, WAFERSORT_YILED, MARKING, ASSEMBLY_LOT_NOS, ASSEMBLY_IN_QTY, ASSEMBLY_YIELD, TEST_QTY_IN, TEST_DATE_IN,BIN1,BIN2,BIN3,BIN4,BIN5, GOOD_UNITS, UNCONUT_UNITS, TEST_YIELD, TESTER, TEST_PGM, SCAN_OUTQTY, SCAN_YIELD, CUM_YIELD)
			VALUES( @FAB_LOT, @FAB_OUT_QTY, @DIE_REV, CONVERT(VARCHAR,@FAB_DATE_OUT,101), CONVERT(VARCHAR,@SORT_DATE_OUT,101), @SORTTEST_PGM, @GOOD_DIE, @SORT_YIELD,@MARKING,@ASSEMBLY_LOT,@TOTALASSY_INQTY,@ASSY_YIELD, @TOTALTEST_INQTY, CONVERT(VARCHAR,@TEST_DATEIN,101), @BIN1,@BIN2,@BIN3,@BIN4,@BIN5, @GOOD_UNITS , @UNCOUNT_UNITS, @TEST_YIELD, @TESTER, @TEST_PGM, @SCAN_OUTQTY,@SCAN_YIELD, @CUM_YIELD )
		--SELECT * FROM @results
		
	
		set @ps_type		= null
		SET @fab_out_qty	= null
		SET @BIN_RECEIVE_WAFER_QTY = null
		SET @fab_date_out	= null
		SET @SORT_DATE_OUT	= null
		SET @GOOD_DIE		= null
		SET @GROSS_DIE		= null
		SET @SORTTEST_PGM	= null
		SET @SORT_YIELD		= null
		SET @ASSEMBLY_LOT	= null
		SET @TOTALASSY_INQTY	= null
		SET @ASSY_YIELD		= null
		SET @TOTALTEST_INQTY	= null
		SET @TEST_DATEIN	= null
		SET @MARKING		= null
		SET @TEST_PGM		= null
		SET @BIN1		= null
		SET @BIN2		= null
		SET @BIN3		= null
		SET @BIN4		= null
		SET @BIN5		= null
		SET @GOOD_UNITS		= null
		SET @UNCOUNT_UNITS  	= null
		SET @TEST_YIELD		= null
		SET @TESTER		= null
		SET @SCAN_OUTQTY	= null
		SET @SCAN_YIELD		= NULL
		SET @CUM_YIELD		= null
		SET @fab_lot		= null
		SET @PROCESSSTEP_ID 	= null
	
		FETCH NEXT FROM @LOT_ID_CURSOR INTO @FAB_LOT,@PROCESSSTEP_ID 
	end
	
	close @lot_id_cursor
	deallocate @lot_id_cursor

	SELECT WAFER_LOT_NO, FAB_OUT_WAFERS, ISNULL(DIE_REV, '') AS DIE_REV ,WAFER_OUTDATE, WAFERSORT_DATE, ISNULL(WAFERSORT_TEST_PGM,'') AS WAFERSORT_TEST_PGM, ISNULL(TOTAL_GOOD_DIE_QTY,'') AS TOTAL_GOOD_DIE_QTY, ISNULL(WAFERSORT_YILED,'') AS WAFERSORT_YILED, MARKING, ASSEMBLY_LOT_NOS, ISNULL(ASSEMBLY_IN_QTY,'') AS ASSEMBLY_IN_QTY, 
	ISNULL(ASSEMBLY_YIELD,'') AS ASSEMBLY_YIELD, TEST_QTY_IN, TEST_DATE_IN,BIN1,BIN2,BIN3,BIN4,BIN5, GOOD_UNITS, UNCONUT_UNITS, ISNULL(TEST_YIELD,'') AS TEST_YIELD, TESTER, TEST_PGM, ISNULL(SCAN_OUTQTY,'') AS SCAN_OUTQTY , ISNULL(SCAN_YIELD,'') AS SCAN_YIELD, ISNULL(CUM_YIELD,'') AS CUM_YIELD FROM @results ORDER BY WAFER_LOT_NO
end